﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace syc.Juece.Report
{
    public partial class 公司生产进度表 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected double ytaojihua = 0;//月套管计划
        protected double y1taojihua = 0;//月一分厂套管计划
        protected double y2taojihua = 0;//月二分厂套管计划    
        protected double y1taon80jihua = 0;//月一分厂套管n80计划

        protected double yyoujihua = 0;//月油管计划
        protected double y2youjihua = 0;//月二分厂油管计划
        protected double y5youjihua = 0;//月五分厂油管计划

        protected double yyjiegujihua = 0;//月油管接箍计划（三分厂）
        protected double ytjiegujihua = 0;//月套管接箍计划（三分厂）
        protected double ybaohuqijihua = 0;//月保护器计划（四分厂）

        protected double ntaojihua = 0;//年套管计划
        protected double n1taojihua = 0;//年一分厂套管计划
        protected double n2taojihua = 0;//年二分厂套管计划   
        protected double n1taon80jihua = 0;//年一分厂套管n80计划  

        protected double nyoujihua = 0;//年油管计划
        protected double n2youjihua = 0;//月二分厂油管计划
        protected double n5youjihua = 0;//月五分厂油管计划

        protected double nyjiegujihua = 0;//年油管接箍计划（三分厂）
        protected double ntjiegujihua = 0;//年套管接箍计划（三分厂）
        protected double nbaohuqijihua = 0;//年保护器计划（四分厂）


        protected double dtao = 0;//套管当日入库量
        protected double d1tao = 0;//一分厂套管当日入库量
        protected double d2tao = 0;//二分厂套管当日入库量
        protected double d1taon80 = 0;//一分厂套管N80当日入库量

        protected double dyou = 0;//油管当日入库量
        protected double d2you = 0;//二分厂当日入库量
        protected double d5you = 0;//五分厂当日入库量
        protected int dyjiegu = 0;//油管接箍当日入库量（三分厂）
        protected int dtjiegu = 0;//套管接箍当日入库量（三分厂）
        protected int dbaohuqi = 0;//内保护器当日入库量（四分厂）


        protected double mtao = 0;//套管当月累计
        protected double m1tao = 0;//一分厂套管当月累计
        protected double m2tao = 0;//二分厂套管当月累计
        protected double m1taon80 = 0;//一分厂套管n80当月累计

        protected double myou = 0;//油管当月累计
        protected double m2you = 0;//二分厂当月累计
        protected double m5you = 0;//五分厂当月累计
        protected int myjiegu = 0;//油管接箍当月累计（三分厂）
        protected int mtjiegu = 0;//套管接箍当月累计（三分厂）
        protected int mbaohuqi = 0;//内保护器当月累计（四分厂）


        protected double ytao = 0;//套管当年累计
        protected double y1tao = 0;//一分厂套管当年累计
        protected double y2tao = 0;//二分厂套管当年累计
        protected double y1taon80 = 0;//一分厂套管n80当年累计

        protected double yyou = 0;//油管当年累计
        protected double y2you = 0;//二分厂当年累计
        protected double y5you = 0;//五分厂当年累计
        protected int yyjiegu = 0;//油管接箍当年累计（三分厂）
        protected int ytjiegu = 0;//套管接箍当年累计（三分厂）
        protected int ybaohuqi = 0;//内保护器年月累计（四分厂）


        protected double mtao完成率 = 0;//套管当月完成率
        protected double m1tao完成率 = 0;//一分厂套管当月完成率
        protected double m2tao完成率 = 0;//二分厂套管当月完成率
        protected double m1taon80完成率 = 0;//一分厂套管n80当月完成率
        protected double myou完成率 = 0;//油管当月完成率
        protected double m2you完成率 = 0;//二分厂当月完成率
        protected double m5you完成率 = 0;//五分厂当月完成率
        protected double myjiegu完成率 = 0;//油管接箍当月完成率（三分厂）
        protected double mtjiegu完成率 = 0;//套管接箍当月完成率（三分厂）
        protected double mbaohuqi完成率 = 0;//内保护器当月完成率（四分厂）


        protected double ytao完成率 = 0;//套管当年完成率
        protected double y1tao完成率 = 0;//一分厂套管当年完成率
        protected double y2tao完成率 = 0;//二分厂套管当年完成率
        protected double y1taon80完成率 = 0;//一分厂套管n80当年完成率
        protected double yyou完成率 = 0;//油管当月完成率
        protected double y2you完成率 = 0;//二分厂当年完成率
        protected double y5you完成率 = 0;//五分厂当年完成率        
        protected double ytjiegu完成率 = 0;//套管接箍当年完成率（三分厂）
        protected double yyjiegu完成率 = 0;//油管接箍当年完成率（三分厂）
        protected double ybaohuqi完成率 = 0;//内保护器当年完成率（四分厂）


        protected double 油套管月完成率 = 0;//外护器当当年完成率（四分厂）
        protected double 油套管年完成率 = 0;//外护器当当年完成率（四分厂）


        protected double 带钢年计划 = 0;
        protected double 带钢月计划 = 0;
        protected double 带钢当日 = 0;
        protected double 带钢本月 = 0;
        protected double 带钢本年 = 0;
        protected double 带钢月完成率 = 0;
        protected double 带钢年完成率 = 0;

        protected double 一分厂合计月完成率 = 0;
        protected double 一分厂合计年完成率 = 0;

        protected double[] tao1 = new double[4];
        protected double[] tao2 = new double[4];
        protected double[] you2 = new double[4];
        protected double[] you5 = new double[4];
        protected int[] liang = new int[9];
        protected int[] zhi = new int[12];

        protected int month;
        protected int year;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.Year0.SelectedValue = DateTime.Today.Year.ToString();
                this.Month0.SelectedValue = DateTime.Today.Month.ToString();
                RiQi.Text = DateTime.Today.AddDays(-1).ToString("yyyy年M月d日");
            }
            Button_Query_Click(null, null);
        }

        protected void WriteToFile()
        {
            DateTime d;
            if (!DateTime.TryParse(this.RiQi.Text, out d))
            {
                d = DateTime.Today;
            }
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + d.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/JueCe/模板/公司生产进度表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "陕西延长石油材料公司生产进度表";
            hssfworkbook.SummaryInformation = si;
        }

        protected void Button_Save_Click(object sender, EventArgs e)
        {

            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("陕西延长石油材料公司生产进度表");
            sheet1.GetRow(1).GetCell(0).SetCellValue("填报日期：" + this.RiQi.Text);
            Button_Query_Click(null, null);
            sheet1.GetRow(4).GetCell(3).SetCellValue(ntaojihua / 10000);
            sheet1.GetRow(4).GetCell(4).SetCellValue(ytaojihua);
            sheet1.GetRow(4).GetCell(5).SetCellValue(dtao);
            sheet1.GetRow(4).GetCell(6).SetCellValue(mtao);
            sheet1.GetRow(4).GetCell(7).SetCellValue(ytao);
            sheet1.GetRow(4).GetCell(8).SetCellValue(mtao完成率.ToString("0.0"));
            sheet1.GetRow(4).GetCell(9).SetCellValue(ytao完成率.ToString("0.0"));

            sheet1.GetRow(5).GetCell(3).SetCellValue(n1taojihua / 10000);
            sheet1.GetRow(5).GetCell(4).SetCellValue(y1taojihua);
            sheet1.GetRow(5).GetCell(5).SetCellValue(d1tao);
            sheet1.GetRow(5).GetCell(6).SetCellValue(m1tao);
            sheet1.GetRow(5).GetCell(7).SetCellValue(y1tao);
            sheet1.GetRow(5).GetCell(8).SetCellValue(m1tao完成率.ToString("0.0"));
            sheet1.GetRow(5).GetCell(9).SetCellValue(y1tao完成率.ToString("0.0"));

            sheet1.GetRow(6).GetCell(3).SetCellValue(n1taon80jihua / 10000);
            sheet1.GetRow(6).GetCell(4).SetCellValue(y1taon80jihua);
            sheet1.GetRow(6).GetCell(5).SetCellValue(d1taon80);
            sheet1.GetRow(6).GetCell(6).SetCellValue(m1taon80);
            sheet1.GetRow(6).GetCell(7).SetCellValue(y1taon80);
            sheet1.GetRow(6).GetCell(8).SetCellValue(m1taon80完成率.ToString("0.0"));
            sheet1.GetRow(6).GetCell(9).SetCellValue(y1taon80完成率.ToString("0.0"));

            sheet1.GetRow(7).GetCell(3).SetCellValue((n1taojihua + n1taon80jihua) / 10000);
            sheet1.GetRow(7).GetCell(4).SetCellValue(y1taojihua + y1taon80jihua);
            sheet1.GetRow(7).GetCell(5).SetCellValue(d1tao + d1taon80);
            sheet1.GetRow(7).GetCell(6).SetCellValue(m1tao + m1taon80);
            sheet1.GetRow(7).GetCell(7).SetCellValue(y1tao + y1taon80);
            sheet1.GetRow(7).GetCell(8).SetCellValue(一分厂合计月完成率.ToString("0.0"));
            sheet1.GetRow(7).GetCell(9).SetCellValue(一分厂合计年完成率.ToString("0.0"));

            sheet1.GetRow(8).GetCell(3).SetCellValue(n2taojihua / 10000);
            sheet1.GetRow(8).GetCell(4).SetCellValue(y2taojihua);
            sheet1.GetRow(8).GetCell(5).SetCellValue(d2tao);
            sheet1.GetRow(8).GetCell(6).SetCellValue(m2tao);
            sheet1.GetRow(8).GetCell(7).SetCellValue(y2tao);
            sheet1.GetRow(8).GetCell(8).SetCellValue(m2tao完成率.ToString("0.0"));
            sheet1.GetRow(8).GetCell(9).SetCellValue(y2tao完成率.ToString("0.0"));

            sheet1.GetRow(9).GetCell(3).SetCellValue(nyoujihua / 10000);
            sheet1.GetRow(9).GetCell(4).SetCellValue(yyoujihua);
            sheet1.GetRow(9).GetCell(5).SetCellValue(dyou);
            sheet1.GetRow(9).GetCell(6).SetCellValue(myou);
            sheet1.GetRow(9).GetCell(7).SetCellValue(yyou);
            sheet1.GetRow(9).GetCell(8).SetCellValue(myou完成率.ToString("0.0"));
            sheet1.GetRow(9).GetCell(9).SetCellValue(yyou完成率.ToString("0.0"));

            sheet1.GetRow(10).GetCell(3).SetCellValue(n2youjihua / 10000);
            sheet1.GetRow(10).GetCell(4).SetCellValue(y2youjihua);
            sheet1.GetRow(10).GetCell(5).SetCellValue(d2you);
            sheet1.GetRow(10).GetCell(6).SetCellValue(m2you);
            sheet1.GetRow(10).GetCell(7).SetCellValue(y2you);
            sheet1.GetRow(10).GetCell(8).SetCellValue(m2you完成率.ToString("0.0"));
            sheet1.GetRow(10).GetCell(9).SetCellValue(y2you完成率.ToString("0.0"));

            sheet1.GetRow(11).GetCell(3).SetCellValue(n5youjihua / 10000);
            sheet1.GetRow(11).GetCell(4).SetCellValue(y5youjihua);
            sheet1.GetRow(11).GetCell(5).SetCellValue(d5you);
            sheet1.GetRow(11).GetCell(6).SetCellValue(m5you);
            sheet1.GetRow(11).GetCell(7).SetCellValue(y5you);
            sheet1.GetRow(11).GetCell(8).SetCellValue(m5you完成率.ToString("0.0"));
            sheet1.GetRow(11).GetCell(9).SetCellValue(y5you完成率.ToString("0.0"));

            sheet1.GetRow(12).GetCell(3).SetCellValue((nyoujihua + ntaojihua) / 10000);
            sheet1.GetRow(12).GetCell(4).SetCellValue(yyoujihua + ytaojihua);
            sheet1.GetRow(12).GetCell(5).SetCellValue(dyou + dtao);
            sheet1.GetRow(12).GetCell(6).SetCellValue(myou + mtao);
            sheet1.GetRow(12).GetCell(7).SetCellValue(yyou + ytao);
            sheet1.GetRow(12).GetCell(8).SetCellValue(油套管月完成率.ToString("0.0"));
            sheet1.GetRow(12).GetCell(9).SetCellValue(油套管年完成率.ToString("0.0"));


            //sheet1.GetRow(8).GetCell(2).SetCellValue(180000);
            //sheet1.GetRow(8).GetCell(3).SetCellValue(7000);
            //sheet1.GetRow(8).GetCell(4).SetCellValue(tao1[0] + tao2[0] + you[0]);
            //sheet1.GetRow(8).GetCell(5).SetCellValue(tao1[1] + tao2[1] + you[1]);
            //sheet1.GetRow(8).GetCell(6).SetCellValue(tao1[2] + tao2[2] + you[2]);
            sheet1.GetRow(16).GetCell(3).SetCellValue(ntjiegujihua);
            sheet1.GetRow(16).GetCell(4).SetCellValue(ytjiegujihua);
            sheet1.GetRow(16).GetCell(5).SetCellValue(dtjiegu);
            sheet1.GetRow(16).GetCell(6).SetCellValue(mtjiegu);
            sheet1.GetRow(16).GetCell(7).SetCellValue(ytjiegu);
            sheet1.GetRow(16).GetCell(8).SetCellValue(mtjiegu完成率.ToString("0.0"));
            sheet1.GetRow(16).GetCell(9).SetCellValue(ytjiegu完成率.ToString("0.0"));

            sheet1.GetRow(17).GetCell(3).SetCellValue(nyjiegujihua);
            sheet1.GetRow(17).GetCell(4).SetCellValue(yyjiegujihua);
            sheet1.GetRow(17).GetCell(5).SetCellValue(dyjiegu);
            sheet1.GetRow(17).GetCell(6).SetCellValue(myjiegu);
            sheet1.GetRow(17).GetCell(7).SetCellValue(yyjiegu);
            sheet1.GetRow(17).GetCell(8).SetCellValue(myjiegu完成率.ToString("0.0"));
            sheet1.GetRow(17).GetCell(9).SetCellValue(yyjiegu完成率.ToString("0.0"));

            sheet1.GetRow(18).GetCell(3).SetCellValue(nbaohuqijihua);
            sheet1.GetRow(18).GetCell(4).SetCellValue(ybaohuqijihua);
            sheet1.GetRow(18).GetCell(5).SetCellValue(dbaohuqi);
            sheet1.GetRow(18).GetCell(6).SetCellValue(mbaohuqi);
            sheet1.GetRow(18).GetCell(7).SetCellValue(ybaohuqi);
            sheet1.GetRow(18).GetCell(8).SetCellValue(mbaohuqi完成率.ToString("0.0"));
            sheet1.GetRow(18).GetCell(9).SetCellValue(ybaohuqi完成率.ToString("0.0"));

            sheet1.GetRow(19).GetCell(3).SetCellValue(带钢年计划);
            sheet1.GetRow(19).GetCell(4).SetCellValue(带钢月计划);
            sheet1.GetRow(19).GetCell(5).SetCellValue(带钢当日);
            sheet1.GetRow(19).GetCell(6).SetCellValue(带钢本月);
            sheet1.GetRow(19).GetCell(7).SetCellValue(带钢本年);
            sheet1.GetRow(19).GetCell(8).SetCellValue(带钢月完成率.ToString("0.0"));
            sheet1.GetRow(19).GetCell(9).SetCellValue(带钢年完成率.ToString("0.0"));



            //string op = (string)this.Session["OperatorName"];
            //if (op != null)
            //{
            //    sheet1.GetRow(9).GetCell(5).SetCellValue(op);
            //}
            //sheet1.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
            month = int.Parse(this.Month0.SelectedValue);
            year = int.Parse(this.Year0.SelectedValue);
            DateTime d1;
            if (!DateTime.TryParse(this.RiQi.Text, out d1))
            {
                this.Message.Text = "·请输入日期";
                return;
            }
            //if (!DateTime.TryParse(this.RiQi2.Text, out d2))
            //{
            //    this.Message.Text = "·请输入累计日期";
            //    return;
            //}
            //if (!DateTime.TryParse(this.RiQi3.Text, out d3))
            //{
            //    this.Message.Text = "·请输入年累计日期";
            //    return;
            //}

            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            //获得月计划
            Cmd.CommandText = @"select isnull(二分厂产品1,0),isnull(五分厂产品1,0),isnull(一分厂产品2,0),isnull(二分厂产品2,0),isnull(三分厂产品1,0),isnull(三分厂产品2,0),isnull(四分厂产品2,0),isnull(一分厂产品4,0) from 生产计划 where 年度='" + year + "' and 月份='" + month + "'";
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                //油管量
                y2youjihua = Reader.GetDouble(0);
                y5youjihua = Reader.GetDouble(1);
                //套管量
                y1taojihua = Reader.GetDouble(2);
                y2taojihua = Reader.GetDouble(3);
                //油接箍
                yyjiegujihua = Reader.GetDouble(4);
                ytjiegujihua = Reader.GetDouble(5);
                ybaohuqijihua = Reader.GetDouble(6);
                //套管n80
                y1taon80jihua = Reader.GetDouble(7);
            }
            Reader.Close();
            yyoujihua = y2youjihua + y5youjihua;
            ytaojihua = y1taojihua + y2taojihua + y1taon80jihua;

            //获得年计划
            Cmd.CommandText = @"select isnull(二分厂产品1,0),isnull(五分厂产品1,0),isnull(一分厂产品2,0),isnull(二分厂产品2,0),isnull(三分厂产品1,0),isnull(三分厂产品2,0),isnull(四分厂产品2,0),isnull(一分厂产品4,0) from 生产计划 where 年度='" + year + "' and 月份=0";
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                //油管量
                n2youjihua = Reader.GetDouble(0);
                n5youjihua = Reader.GetDouble(1);
                //套管量
                n1taojihua = Reader.GetDouble(2);
                n2taojihua = Reader.GetDouble(3);
                //油接箍
                nyjiegujihua = Reader.GetDouble(4);
                ntjiegujihua = Reader.GetDouble(5);
                nbaohuqijihua = Reader.GetDouble(6);
                //套管n80
                n1taon80jihua = Reader.GetDouble(7);
            }
            Reader.Close();
            nyoujihua = n2youjihua + n5youjihua;
            ntaojihua = n1taojihua + n2taojihua + n1taon80jihua;
            //---------------------------
            //当日入库量

            string sql = @"
            declare @d2you float
            declare @d5you float
            declare @d1tao float
            declare @d2tao float
            declare @d1taon80 float
            declare @dyjiegu int
            declare @dtjiegu int
            declare @dbaohuqi int         
            declare @riqi smalldatetime
            select  @d2you=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=2 and 产品类别ID=1 and 日期='" + DateTime.Parse(this.RiQi.Text) + "'  select @d5you=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=5 and 产品类别ID=1 and 日期='" + DateTime.Parse(this.RiQi.Text) + "' select @d1tao=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=1 and 产品类别ID=2 and 日期='" + DateTime.Parse(this.RiQi.Text) + "' select @d2tao=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=2 and 产品类别ID=2 and 日期='" + DateTime.Parse(this.RiQi.Text) + "'select @dyjiegu=isnull(sum(磷化支数),0) from 生产进度3 where [产品类别ID]=1 and 日期='" + DateTime.Parse(this.RiQi.Text) + "' select @dtjiegu=isnull(sum(磷化支数),0) from 生产进度3 where [产品类别ID]=2 and 日期='" + DateTime.Parse(this.RiQi.Text) + "'  select @dbaohuqi=isnull(sum(入库),0) from 生产进度4 where 日期='" + DateTime.Parse(this.RiQi.Text) + "' select  @d1taon80=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=1 and 产品类别ID=4 and 日期='" + DateTime.Parse(this.RiQi.Text) + "' select @d2you,@d5you,@d1tao,@d2tao,@dyjiegu,@dtjiegu,@dbaohuqi,@d1taon80";

            Cmd.CommandText = sql;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                //油管量
                d2you = Reader.GetDouble(0);
                d5you = Reader.GetDouble(1);
                //套管量
                d1tao = Reader.GetDouble(2);
                d2tao = Reader.GetDouble(3);
                //油接箍
                dyjiegu = Reader.GetInt32(4);
                dtjiegu = Reader.GetInt32(5);
                dbaohuqi = Reader.GetInt32(6);
                //套管N80量
                d1taon80 = Reader.GetDouble(7);
            }
            Reader.Close();
            dyou = d2you + d5you;
            dtao = d1tao + d2tao + d1taon80;
            //---------------------------
            //当月入库量累计

            string sql1 = @"
            declare @m2you float
            declare @m5you float
            declare @m1tao float
            declare @m2tao float
            declare @m1taon80 float
            declare @myjiegu int
            declare @mtjiegu int
            declare @mbaohuqi int
           
           
            declare @1yc smalldatetime 
           
            declare @2yc smalldatetime 
          
            declare @3yc smalldatetime 
           
            declare @4yc smalldatetime 
           
            declare @5yc smalldatetime 
          
            select  @1yc=起始日期 from  生产月设置 where 分厂=1 and 年度='" + year + "' and 月份='" + month + "' select distinct @2yc=起始日期 from  生产月设置 where 分厂=2 and 年度='" + year + "' and 月份='" + month + "' select distinct @3yc=起始日期 from  生产月设置 where 分厂=3 and 年度='" + year + "' and 月份='" + month + "' select distinct @4yc=起始日期 from  生产月设置 where 分厂=4 and 年度='" + year + "' and 月份='" + month + "' select distinct  @5yc=起始日期 from 生产月设置  where 分厂=5 and 年度='" + year + "' and 月份='" + month + "' select  @m2you=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=2 and 产品类别ID=1 and 日期 between @2yc and '" + DateTime.Parse(this.RiQi.Text) + "' select  @m5you=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=5 and 产品类别ID=1 and 日期 between @5yc and '" + DateTime.Parse(this.RiQi.Text) + "' select  @m1tao=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=1 and 产品类别ID=2 and 日期 between @1yc and '" + DateTime.Parse(this.RiQi.Text) + "' select  @m2tao=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=2 and 产品类别ID=2 and 日期 between @2yc and '" + DateTime.Parse(this.RiQi.Text) + "' select @myjiegu=isnull(sum(磷化支数),0) from 生产进度3 where [产品类别ID]=1 and 日期 between @3yc and '" + DateTime.Parse(this.RiQi.Text) + "' select @mtjiegu=isnull(sum(磷化支数),0) from 生产进度3 where [产品类别ID]=2 and 日期 between @3yc and '" + DateTime.Parse(this.RiQi.Text) + "'  select @mbaohuqi=isnull(sum(入库),0) from 生产进度4 where 日期 between @4yc and '" + DateTime.Parse(this.RiQi.Text) + "' select  @m1taon80=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=1 and 产品类别ID=4 and 日期 between @1yc and '" + DateTime.Parse(this.RiQi.Text) + "' select @m2you,@m5you,@m1tao,@m2tao,@myjiegu,@mtjiegu,@mbaohuqi,@m1taon80";

            Cmd.CommandText = sql1;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                //油管量
                m2you = Reader.GetDouble(0);
                m5you = Reader.GetDouble(1);
                //套管量
                m1tao = Reader.GetDouble(2);
                m2tao = Reader.GetDouble(3);
                //油接箍
                myjiegu = Reader.GetInt32(4);
                mtjiegu = Reader.GetInt32(5);
                mbaohuqi = Reader.GetInt32(6);
                //套管n80量
                m1taon80 = Reader.GetDouble(7);
            }
            Reader.Close();
            myou = m2you + m5you;
            mtao = m1tao + m2tao + m1taon80;

            //当年入库量累计
            string sql2 = @"
            declare @y2you float
            declare @y5you float
            declare @y1tao float
            declare @y2tao float
            declare @y1taon80 float
            declare @yyjiegu int
            declare @ytjiegu int
            declare @ybaohuqi int
          
            declare @1nc smalldatetime 
           
            declare @2nc smalldatetime 
           
            declare @3nc smalldatetime 
           
            declare @4nc smalldatetime 
            
            declare @5nc smalldatetime 
            
            select  @1nc=(select distinct 起始日期 from  生产月设置 where 分厂=1 and 年度='" + year + "' and 月份=1) select  @2nc=(select distinct 起始日期 from  生产月设置 where 分厂=2 and 年度='" + year + "' and 月份=1) select  @3nc=(select distinct 起始日期 from 生产月设置 where 分厂=3 and 年度='" + year + "' and 月份=1) select  @4nc=(select distinct 起始日期 from  生产月设置 where 分厂=4 and 年度='" + year + "' and 月份=1) select @5nc=(select distinct 起始日期 from 生产月设置  where 分厂=5 and 年度='" + year + "' and 月份=1) select  @y2you=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=2 and 产品类别ID=1 and 日期 between @2nc and '" + DateTime.Parse(this.RiQi.Text) + "' select  @y5you=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=5 and 产品类别ID=1 and 日期 between @5nc and '" + DateTime.Parse(this.RiQi.Text) + "' select  @y1tao=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=1 and 产品类别ID=2 and 日期 between @1nc and '" + DateTime.Parse(this.RiQi.Text) + "' select  @y2tao=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=2 and 产品类别ID=2 and 日期 between @2nc and '" + DateTime.Parse(this.RiQi.Text) + "' select @yyjiegu=isnull(sum(磷化支数),0) from 生产进度3 where [产品类别ID]=1 and 日期 between @3nc and '" + DateTime.Parse(this.RiQi.Text) + "' select @ytjiegu=isnull(sum(磷化支数),0) from 生产进度3 where [产品类别ID]=2 and 日期 between @3nc and '" + DateTime.Parse(this.RiQi.Text) + "'  select @ybaohuqi=isnull(sum(入库),0) from 生产进度4 where 日期 between @4nc and '" + DateTime.Parse(this.RiQi.Text) + "' select  @y1taon80=round(isnull(sum(入库重量),0)/1000,3) from 生产进度 where 分厂ID=1 and 产品类别ID=4 and 日期 between @1nc and '" + DateTime.Parse(this.RiQi.Text) + "' select @y2you,@y5you,@y1tao,@y2tao,@yyjiegu,@ytjiegu,@ybaohuqi,@y1taon80 ";

            Cmd.CommandText = sql2;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                //油管量
                y2you = Reader.GetDouble(0);
                y5you = Reader.GetDouble(1);
                //套管量
                y1tao = Reader.GetDouble(2);
                y2tao = Reader.GetDouble(3);
                //油接箍
                yyjiegu = Reader.GetInt32(4);
                ytjiegu = Reader.GetInt32(5);
                ybaohuqi = Reader.GetInt32(6);
                //套管n80量
                y1taon80 = Reader.GetDouble(7);
            }
            Reader.Close();
            yyou = y2you + y5you;
            ytao = y1tao + y1taon80 + y2tao;
            if (ytaojihua == 0)
            {
                mtao完成率 = 0;
            }
            else
            {
                mtao完成率 = ((mtao / ytaojihua) * 100);
            }
            if (y1taojihua == 0)
            {
                m1tao完成率 = 0;
            }
            else
            {
                m1tao完成率 = ((m1tao / y1taojihua) * 100);
            }
            if (y1taon80jihua == 0)
            {
                m1taon80完成率 = 0;
            }
            else
            {
                m1taon80完成率 = ((m1taon80 / y1taon80jihua) * 100);
            }
            if ((y1taojihua + y1taon80jihua) == 0)
            {
                一分厂合计月完成率 = 0;
            }
            else
            {
                一分厂合计月完成率 = (((m1tao + m1taon80) / (y1taojihua + y1taon80jihua)) * 100);
            }
            if (y2taojihua == 0)
            {
                m2tao完成率 = 0;
            }
            else
            {
                m2tao完成率 = ((m2tao / y2taojihua) * 100);
            }
            if (yyoujihua == 0)
            {
                myou完成率 = 0;
            }
            else
            {
                myou完成率 = ((myou / yyoujihua) * 100);
            }
            if (y2youjihua == 0)
            {
                m2you完成率 = 0;
            }
            else
            {
                m2you完成率 = ((m2you / y2youjihua) * 100);
            }
            if (y5youjihua == 0)
            {
                m5you完成率 = 0;
            }
            else
            {
                m5you完成率 = ((m5you / y5youjihua) * 100);
            }
            if (yyjiegujihua == 0)
            {
                myjiegu完成率 = 0;
            }
            else
            {
                myjiegu完成率 = ((myjiegu / yyjiegujihua) * 100);
            }
            if (ytjiegujihua == 0)
            {
                mtjiegu完成率 = 0;
            }
            else
            {
                mtjiegu完成率 = ((mtjiegu / ytjiegujihua) * 100);
            }
            if (ybaohuqijihua == 0)
            {
                mbaohuqi完成率 = 0;
            }
            else
            {
                mbaohuqi完成率 = ((mbaohuqi / ybaohuqijihua) * 100);
            }


            //-----------------------------------------
            if (ntaojihua == 0)
            {
                ytao完成率 = 0;
            }
            else
            {
                ytao完成率 = ((ytao / ntaojihua) * 100);
            }
            if (n1taojihua == 0)
            {
                y1tao完成率 = 0;
            }
            else
            {
                y1tao完成率 = ((y1tao / n1taojihua) * 100);
            }
            if (n1taon80jihua == 0)
            {
                y1taon80完成率 = 0;
            }
            else
            {
                y1taon80完成率 = ((y1taon80 / n1taon80jihua) * 100);
            }
            if ((n1taojihua + n1taon80jihua) == 0)
            {
                一分厂合计年完成率 = 0;
            }
            else
            {
                一分厂合计年完成率 = (((y1tao + y1taon80) / (n1taojihua + n1taon80jihua)) * 100);
            }
            if (n2taojihua == 0)
            {
                y2tao完成率 = 0;
            }
            else
            {
                y2tao完成率 = ((y2tao / n2taojihua) * 100);
            }
            if (nyoujihua == 0)
            {
                yyou完成率 = 0;
            }
            else
            {
                yyou完成率 = ((yyou / nyoujihua) * 100);
            }
            if (n2youjihua == 0)
            {
                y2you完成率 = 0;
            }
            else
            {
                y2you完成率 = ((y2you / n2youjihua) * 100);
            }
            if (n5youjihua == 0)
            {
                y5you完成率 = 0;
            }
            else
            {
                y5you完成率 = ((y5you / n5youjihua) * 100);
            }
            if (ntjiegujihua == 0)
            {
                ytjiegu完成率 = 0;
            }
            else
            {
                ytjiegu完成率 = ((ytjiegu / ntjiegujihua) * 100);
            }
            if (nyjiegujihua == 0)
            {
                yyjiegu完成率 = 0;
            }
            else
            {
                yyjiegu完成率 = ((yyjiegu / nyjiegujihua) * 100);
            }
            if (nbaohuqijihua == 0)
            {
                ybaohuqi完成率 = 0;
            }
            else
            {

                ybaohuqi完成率 = ((ybaohuqi / nbaohuqijihua) * 100);
            }
            if ((yyoujihua + ytaojihua) == 0)
            {
                油套管月完成率 = 0;
            }
            else
            {
                油套管月完成率 = ((myou + mtao) / (yyoujihua + ytaojihua) * 100);
            }
            if ((nyoujihua + ntaojihua) == 0)
            {
                油套管年完成率 = 0;
            }
            else
            {
                油套管年完成率 = ((yyou + ytao) / (nyoujihua + ntaojihua) * 100);
            }

            Cmd.CommandText =
@"select cast(isnull(年计划,0) as float) as 年计划
, cast(isnull(月计划,0) as float) as 月计划
,cast(isnull(当日,0) as float) as 当日
,cast(isnull(本月,0) as float) as 本月
,cast(isnull(本年,0) as float) as 本年
from 精密带钢进度 where 日期='" + DateTime.Parse(this.RiQi.Text) + "'";
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                带钢年计划 = Reader.GetDouble(0);
                带钢月计划 = Reader.GetDouble(1);
                带钢当日 = Reader.GetDouble(2);
                带钢本月 = Reader.GetDouble(3);
                带钢本年 = Reader.GetDouble(4);
                if (带钢月计划 > 0)
                {
                    带钢月完成率 = 带钢本月 / 带钢月计划 * 100;
                }
                if (带钢年计划 > 0)
                {
                    带钢年完成率 = 带钢本年 / 带钢年计划 * 100;
                }
            }
            Reader.Close();

            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
        }
    }
}

